package com.sanyog.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.sanyog.d8mb.PostAdBean;

public class DBImpl {

	String sC = "'";
	String sCo = ",";
	public static LoadProp loadProp = LoadProp.getInstance();

	private void createClass() {
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		String foleder_db_name = loadProp.getProp("INIAN_CITY_FOLDER");
		Statement stmt = null;
		Connection conn = null;
		try {
			conn = new DBHelper().getConnection(db_name,foleder_db_name);

			stmt = conn.createStatement();
			//stmt.executeUpdate(DBStatement.POST_AD_ADMIN_USER);
			//stmt.executeUpdate("INSERT INTO postad_admin(UserName,Password,SUPER_ADMIN) VALUES('AjayindianAryan', 'indian11111@a@','Y')");

			//stmt.executeUpdate(DBStatement.CREATE_POST_AD);
			stmt.executeUpdate(DBStatement.CREATE_RATING_TABLE);
			
			// stmt.executeUpdate(DBStatement.CREATE_USER);
			stmt.execute("SHUTDOWN");
			System.out.println("DONE................");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		finally {
			new DBHelper().closeDBConnection(conn, null, stmt);
		}
	}

	public String isAdmin(String un, String pass) {
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		String foleder_db_name = loadProp.getProp("INIAN_CITY_FOLDER");
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;
		String msg = null;
		conn = new DBHelper().getConnection(db_name,foleder_db_name);
		try {
			ps = conn.prepareStatement("SELECT UserName,SUPER_ADMIN from postad_admin where UserName=? and Password=?");
			
			ps.setString(1, un);
			ps.setString(2, pass);
			rs = ps.executeQuery();
			if (rs.next()) {
				if (rs.getString(2) != null)
					msg = "SUPER_ADMIN";
				else
					msg = "ADMIN";

			} else {
				msg = "NON_ADMIN";
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBHelper().closeDBConnection(conn, rs, ps);
		}

		return msg;

	}

	public boolean addRating(String name, String email, String rating) {
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		//System.out.println("*******************************************"+this.getClass().getResourceAsStream("/com/sanyog/util/config.properties").);
		String foleder_db_name = "com//sanyog//util//";//loadProp.getProp("INIAN_CITY_FOLDER");
		System.out.println(db_name);
		System.out.println(foleder_db_name);
		PreparedStatement ps = null;
		Connection conn = null;
		Connection conn1 = null;
		Statement stmt1 = null;
		ResultSet rs1 = null;
		boolean flag = false;
		conn = new DBHelper().getConnection(db_name,foleder_db_name);
		try {
			createClass();
			ps = conn.prepareStatement("INSERT INTO rating(Name,Email,Rating) VALUES(?,?,?)");
			ps.setString(1, name);
			ps.setString(2, email);
			ps.setString(2, rating);
			ps.execute();
			flag = true;

		} catch (SQLException e) {
			flag = false;
			e.printStackTrace();
		}
		
		String exp = "Select * from rating";
		System.out.println("This exp for category............" + exp);
		System.out.println("xkxk");
		try {
			conn1 = new DBHelper().getConnection(db_name,foleder_db_name);
			stmt1 = conn.createStatement();
			rs1 = stmt1.executeQuery(exp);

			while (rs1.next()) {
				System.out.println(rs1.getString("Name"));
				System.out.println(rs1.getString("Email"));
				System.out.println(rs1.getString("Rating"));
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBHelper().closeDBConnection(conn1, rs1, stmt1);
		}
		System.out.println(flag);
		return flag;

	}

	/**
	 * get post as per category
	 * 
	 * @param category
	 * @return
	 */
	public List getAdAsCategory(String category) {
		List postList = new ArrayList();
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		String foleder_db_name = loadProp.getProp("INIAN_CITY_FOLDER");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String ex = sC + category + sC;
		String exp = "Select * from postad_ad where Category=" + ex + "";
		System.out.println("This exp for category............" + exp);
		System.out.println("xkxk");
		try {
			conn = new DBHelper().getConnection(db_name,foleder_db_name);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(exp);

			while (rs.next()) {

				PostAdBean postAdBean = new PostAdBean();

				postAdBean.setCategory(rs.getString("Category"));
				postAdBean.setEmail(rs.getString("Email"));
				postAdBean.setMobileno(rs.getString("mobileNo"));
				postAdBean.setUsername(rs.getString("Username"));
				postAdBean.setState(rs.getString("state"));
				postAdBean.setWeblink(rs.getString("Weblink"));
				postAdBean.setTitle(rs.getString("Title"));
				postAdBean.setPostDate(rs.getString("POSTAD_DATE"));
				postAdBean.setDescription(rs.getString("Description"));
				postList.add(postAdBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBHelper().closeDBConnection(conn, rs, stmt);
		}
		return postList;
	}

	public List getAllAd() {
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		String foleder_db_name = loadProp.getProp("INIAN_CITY_FOLDER");
		createClass();
		List postList = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String exp = "Select * from postad_ad";

		try {
			conn = new DBHelper().getConnection(db_name,foleder_db_name);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(exp);

			while (rs.next()) {

				PostAdBean postAdBean = new PostAdBean();

				postAdBean.setCategory(rs.getString("Category"));
				postAdBean.setEmail(rs.getString("Email"));
				postAdBean.setMobileno(rs.getString("mobileNo"));
				postAdBean.setUsername(rs.getString("Username"));
				postAdBean.setState(rs.getString("state"));
				postAdBean.setWeblink(rs.getString("Weblink"));
				postAdBean.setTitle(rs.getString("Title"));
				postAdBean.setPostDate(rs.getString("POSTAD_DATE"));
				postAdBean.setDescription(rs.getString("Description"));
				postList.add(postAdBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBHelper().closeDBConnection(conn, rs, stmt);
		}
		return postList;

	}

	public PostAdBean getDescriptionAsEmail(String email, String title) {
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		String foleder_db_name = loadProp.getProp("INIAN_CITY_FOLDER");
		Connection conn = null;
		Statement stmt = null;
		String ex = sC + email + sC;
		String ex1 = sC + title + sC;
		String exp = "Select * from postad_ad where Email=" + ex
				+ " and Title=" + ex1 + "";
		System.out.println("This exp for category............" + exp);

		PostAdBean postAdBean = null;
		ResultSet rs = null;
		try {
			conn = new DBHelper().getConnection(db_name,foleder_db_name);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(exp);

			while (rs.next()) {
				postAdBean = new PostAdBean();
				postAdBean.setCategory(rs.getString("Category"));
				postAdBean.setEmail(rs.getString("Email"));
				postAdBean.setMobileno(rs.getString("mobileNo"));
				postAdBean.setUsername(rs.getString("Username"));
				postAdBean.setState(rs.getString("state"));
				postAdBean.setWeblink(rs.getString("Weblink"));
				postAdBean.setTitle(rs.getString("Title"));
				postAdBean.setPostDate(rs.getString("POSTAD_DATE"));
				postAdBean.setDescription(rs.getString("Description"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBHelper().closeDBConnection(conn, rs, stmt);
		}
		return postAdBean;

	}

	public List getAdAsState(String state) {
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		String foleder_db_name = loadProp.getProp("INIAN_CITY_FOLDER");
		List postList = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String ex = sC + state + sC;
		String exp = "Select * from postad_ad where state=" + ex + "";
		System.out.println("This exp for category............" + exp);

		try {
			conn = new DBHelper().getConnection(db_name,foleder_db_name);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(exp);

			while (rs.next()) {

				PostAdBean postAdBean = new PostAdBean();

				postAdBean.setCategory(rs.getString("Category"));
				postAdBean.setEmail(rs.getString("Email"));
				postAdBean.setMobileno(rs.getString("mobileNo"));
				postAdBean.setUsername(rs.getString("Username"));
				postAdBean.setState(rs.getString("state"));
				postAdBean.setWeblink(rs.getString("Weblink"));
				postAdBean.setTitle(rs.getString("Title"));
				postAdBean.setPostDate(rs.getString("POSTAD_DATE"));
				postAdBean.setDescription(rs.getString("Description"));
				postList.add(postAdBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBHelper().closeDBConnection(conn, rs, stmt);
		}
		return postList;
	}

	public  boolean insertToPostAD(PostAdBean postAdBean) {
		//createClass();
		boolean flag = false;
		Statement stmt = null;
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		String foleder_db_name = loadProp.getProp("INIAN_CITY_FOLDER");
		Connection conn = null;
		PreparedStatement psmt=null;
		try {

			String ex2="INSERT INTO postad_ad(UserName,Email,mobileNo,Title,Description,Category,state,Weblink,POSTAD_DATE) VALUES(?,?,?,?,?,?,?,?,Sysdate)";

			System.out.println(ex2);
			conn = new DBHelper().getConnection(db_name,foleder_db_name);
			psmt=conn.prepareStatement(ex2);
			psmt.setString(1, postAdBean.getUsername());
			psmt.setString(2, postAdBean.getEmail());
			psmt.setString(3, postAdBean.getMobileno());
			psmt.setString(4, postAdBean.getTitle());
			psmt.setString(5, postAdBean.getDescription());
			psmt.setString(6, postAdBean.getCategory());
			psmt.setString(7, postAdBean.getState());
			psmt.setString(8, postAdBean.getWeblink());
			
			/*
			 * try { stmt.executeUpdate(DBStatement.CREATE_POST_AD);
			 * stmt.executeUpdate(DBStatement.CREATE_USER);
			 * System.out.println("DONE................"); } catch (SQLException
			 * sex) { sex.printStackTrace(); }
			 */
			psmt.execute();

			//stmt.execute("SHUTDOWN");
			// createClass();
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBHelper().closeDBConnection(conn, null, psmt);
		}

		return flag;
	}

	


	public List getAllUser() {

		List userList = new ArrayList();
		Connection conn = null;
		PreparedStatement psmt = null;
		String db_name = loadProp.getProp("INDIAN_DB_NAME");
		String foleder_db_name = loadProp.getProp("INIAN_CITY_FOLDER");
		ResultSet rs = null;
		try {
			conn = new DBHelper().getConnection(db_name,foleder_db_name);
			psmt = conn.prepareStatement("Select * from postad_user");

			rs = psmt.executeQuery();
			while (rs.next()) {
				PostAdBean postAdBean = new PostAdBean();
				postAdBean.setUsername(rs.getString("UserName"));
				postAdBean.setEmail(rs.getString("Email"));
				postAdBean.setMobileno(rs.getString("mobileNo"));
				postAdBean.setPostDate(rs.getString("CREATE_DATE"));
				postAdBean.setState(rs.getString("state"));
				userList.add(postAdBean);
			}

		} catch (SQLException e) {

			e.printStackTrace();
		}

		finally {
			new DBHelper().closeDBConnection(conn, rs, psmt);
		}
		return userList;
	}

	public static void main(String[] args) {
		new DBImpl().createClass();
	}

}
